﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for DataBase
/// </summary>
public class DataBase
{
    private SqlConnection mySqlConnection;
    private SqlCommand mySqlCommand;
    private SqlDataAdapter mySqlDataAdapter;
    private DataSet myDataSet;

    public DataBase()
    {
        //
        // TODO: Add constructor logic here
        //string path = "C:\\Users\\Ng Chye Leong\\Documents\\Visual Studio 2010\\Websites\\WebSite2\\App_Data";
        //string path = "C:\\Users\\Ng Chye Leong\\Desktop\\TFS\\Prototype\\Version 2\\App_data";
        //string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + path + "\\stockmarket.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        string connString = "Data Source=.\\SQLEXPRESS;Integrated Security=true;" + "Initial Catalog=stockmarket;";
        //string connString = "Server=.\\SQLEXPRESS; Initial Catalog=stockmarket; Integrated Security=false; User Id=sa; Password=password";
        mySqlConnection = new SqlConnection(connString);
    }

    public DataSet GetSharePlotGraph(string shareSymbol, string gameId)
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "GetSharePlotGraph";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddWithValue("@ShareSymbol", shareSymbol);
            mySqlCommand.Parameters.AddWithValue("@GameID", gameId);

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "sharegraph");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    public int GetGameElapsedSpeed(string gameId)
    {
        int elapsedSpeed = 0;
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "GetGameElapsedSpeed";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddWithValue("@GameID", gameId);

            OpenConnection();
            SqlDataReader reader = mySqlCommand.ExecuteReader();

            if (reader.Read())
                elapsedSpeed = reader.GetInt32(0);

            reader.Dispose();
            reader.Close();

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return elapsedSpeed;
    }

    public void UpdateShareSimulation(int gameID)
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "SimulateWeeklySharePrice";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.AddWithValue("@GameID", gameID);

            OpenConnection();

            mySqlCommand.ExecuteNonQuery();

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }
    }

    public DataSet GetGameStatus()
    {
        DataSet myDataSet;

        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "GetGameStatus";
            mySqlCommand.CommandType = CommandType.StoredProcedure;

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "gamestatus");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    public DataSet GetNumberOfGames()
    {
        DataSet myDataSet;

        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = @"
            SELECT        
            GameID, GameElapsedSpeed
            FROM            
            Game";
            mySqlCommand.CommandType = CommandType.Text;

            mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;
            myDataSet = new DataSet();

            OpenConnection();

            mySqlDataAdapter.Fill(myDataSet, "games");

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }

        return myDataSet;
    }

    public void ResetShareSimulation()
    {
        try
        {
            mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = "ResetShareSimulation";
            mySqlCommand.CommandType = CommandType.StoredProcedure;

            OpenConnection();

            mySqlCommand.ExecuteNonQuery();

            CloseConnection();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }
    }

    private void CloseConnection()
    {
        try
        {
            if (mySqlConnection.State == ConnectionState.Open)
                mySqlConnection.Close();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }
    }

    private void OpenConnection()
    {
        try
        {
            if (mySqlConnection.State != ConnectionState.Open)
                mySqlConnection.Open();
        }
        catch (SqlException ex)
        {
            throw ex;
        }
        catch (Exception ex2)
        {
            throw ex2;
        }
    }
}